/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;
/**
 * @author Miguel
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import BugTracking.Exceptions;
import config.dataServer;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 * Class encargada, a la conexion, y ejecucion de consultas SQL en la base de datos
 * @author MPanuera
 */
public class cntAccesoDatos {
	/*Conexion a Base de Datos*/
    private String user;
    private String pass;
    private String dbclass;
    private String serverURL;
    public final static int INSERT = 1;
    public final static int UPDATE = 2;
    public final static int DELETE = 3;
    public final static int SELECT = 4;
    public final static int INSERT_RETURNING = 5;
    public final static int FUNCTION = 6;

    /* Variables de Entorno */
    private List<String> lstParams = new ArrayList<String>();
    private List<String> lstValues = new ArrayList<String>();
    //private Pair<String, String> lista;
    public String query;
    private String table;
    private String scheme;
    private String where;
    private String order;
    private String group;
    private String ide_returning;
    private int fields;
    private Connection con;
    private int type;
    private dataServer db;
    /**
     * Ingresa la consulta
     * @param q
     * Cualquier tipo de consulta
     */
    public void setQuery(String q){
        query = q;
    }
    /**
     * Obtener la consulta ingresada
     * @return
     * Retorna la consulta a ejecutarse
     */
    public String getQuery(){
        return query;
    }
    public void begin(String tabla){
        begin();
        table = tabla;
    }
    public void begin(){
        lstParams.clear();
        lstValues.clear();
    }
    public void setTable(String t){table = t;}
    public void setReturning(String t){ide_returning = t;}
    public void setWhere(String w){where = w;}
    public void setScheme(String w){scheme = w;}
    public void add(String data, String value){
            lstParams.add(data);
            lstValues.add(value);
            fields++;
    }
    public void add(String data){
            lstParams.add(data);
            fields++;
    }
    /**
     * Nos Permite conectarnos a la Base de Datos
     */
    public cntAccesoDatos(){
        db = dataServer.getInstance();
        user = db.getUser();
        pass = db.getPass();
        dbclass = db.getDBClass();
        serverURL = db.getServerURL();
        //System.out.println(serverURL);
        con=null;
        query=null;
        where="";
        order="";
        group="";
        type='s';
    }
    public dataServer getConexion(){
    	return db;
    }
    /**
     * Tipo de Transaccion
     * @param type
     * insert, update, delete, function, insert-returning, select
     */
    public void setType(int t){
        type = t;
    }
    public void prepare(){
    	Iterator itp =null;
    	Iterator itv =null;
    	String entity=null;
    	entity = table;
    	if (scheme!=null){entity = scheme+"."+entity;}
    	if (type==INSERT || type==INSERT_RETURNING){
            String columns="", values="";
            query="insert into "+entity+" (";
            itp = lstParams.iterator();
            itv = lstValues.iterator();
            while (itp.hasNext() && itv.hasNext()) {
                    columns+=""+itp.next().toString()+",";
                    values+="'"+itv.next().toString()+"',";
            }
            columns = columns.substring(0, columns.length()-1);
            values = values.substring(0, values.length()-1);
            query+=columns+") values (" +values+")";
            if (type==INSERT_RETURNING) query+=" returning " + ide_returning;
        }
        else if (type==UPDATE){
            query+="update "+entity+" set ";
            itp = lstParams.iterator();
            itv = lstValues.iterator();
            while (itp.hasNext() && itv.hasNext()) {
                    query+=itp.next().toString()+" = ";
                    query+="'"+itv.next().toString()+"',";
                    //System.out.println(e.getKey() + " " + e.getValue());
            }
            query = query.substring(0, query.length()-1);
            if (!(where.isEmpty())) query+=" where "+where;
        }
        else if (type==DELETE){
            //query+="delete "+table+" set ";
            query="";
            itp = lstParams.iterator();
            itv = lstValues.iterator();
            while (itp.hasNext() && itv.hasNext()) {
                query+=" delete from "+entity+" where ";
                query+=itp.next().toString()+" = ";
                query+="'"+itv.next().toString()+"';";
                //System.out.println(e.getKey() + " " + e.getValue());
            }
        }
        else if (type==FUNCTION){
            query="select "+entity+"(";
            itp = lstParams.iterator();
            while (itp.hasNext()) {
                    query+=itp.next().toString()+",";
            }
            query = query.substring(0, query.length()-1);
            query+=")";
        }
        else { //type=='s';
            query="select ";
            itp = lstParams.iterator();
            while (itp.hasNext()) {
                query+=itp.next().toString()+",";
            }
            query = query.substring(0, query.length()-1);
            query+=" from "+entity+" ";
            //System.out.println(where);
            if (!(where.isEmpty())) query+=" where "+where;
            if (!(order.isEmpty())) query+=" order by "+order;
            if (!(group.isEmpty())) query+=" group by "+group;
        }
    }
    public void prepare(int tipo){
    	setType(tipo);
    	prepare();
    }
    /**
     * Ejecuta una sentencia SQL
     * @return
     * Retorna una matriz de String's, si hay una consulta sino retorna null
     * @exception Error Guarda detalles en el log
     */
    public String[][] execute() throws Exception{
        String [][] data=null;
        if (con==null) init();
        if (con==null) return data;
        if (query==null) prepare();
        ResultSet rs;
        ResultSetMetaData rsMeta;
        Statement stmt = null;
        if (type==SELECT || type==INSERT_RETURNING){
            try {
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al realizar consulta: "+ex.getMessage());
                throw new Error("<strong>No se pudo realizar la consulta</strong>");
            }
            try {
                rs = stmt.executeQuery(query);
                rsMeta = rs.getMetaData();
                int colCount = rsMeta.getColumnCount();
                int rowCount = 0;
                while (rs.next()) {
                    rowCount++;
                }
                rs.beforeFirst();
                data = new String[rowCount][colCount];
                for (int i=0;rs.next();i++){
                    for (int j=1;j<=colCount;j++){
                        data[i][j-1]=rs.getString(j);
                    }
                }
                stmt.execute("END");
                stmt.close();
                //con.close();
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al ejecutar consulta: "+ex.getMessage()+";"+query);
                throw new Error("<strong>Error al realizar consulta</strong>");
            }
        }
        else{
            try {
                stmt = con.createStatement();
                stmt.executeQuery(query);
                //Cerramos la conexiÃ³n
                stmt.execute("END");
                stmt.close();
                //con.close();
            } catch (SQLException ex) {
                Exceptions exc = new Exceptions();
                exc.write("Error al realizar consulta: "+ex.getMessage());
                throw new Error("<strong>Error al realizar consulta</strong>");
            }
        }
        return data;
    }
    public void close(){
        try {
            con.close();
        } catch (SQLException ex) {
        }
    }
    /**
     * Inicializa la conexion, a la base de datos
     * @exception Error Guarda detalles en el log, sobre el error en la conexion
     */
    public void init(){
        try {
            Class.forName(dbclass);
            con = DriverManager.getConnection(serverURL, user, pass);
            } catch( Exception e ){
            Exceptions ex = new Exceptions();
            ex.write("Error al conectarse a la BD: "+e.getMessage());
            }
    }
}